-- 更新房间当前人数的触发器
CREATE OR REPLACE FUNCTION update_room_occupancy()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 新增学生时增加房间人数
        IF NEW.room_id IS NOT NULL THEN
            UPDATE rooms 
            SET current_num = current_num + 1
            WHERE room_id = NEW.room_id;
        END IF;
    ELSIF TG_OP = 'UPDATE' THEN
        -- 学生换房间时更新两个房间的人数
        IF OLD.room_id IS NOT NULL THEN
            UPDATE rooms 
            SET current_num = current_num - 1
            WHERE room_id = OLD.room_id;
        END IF;
        IF NEW.room_id IS NOT NULL THEN
            UPDATE rooms 
            SET current_num = current_num + 1
            WHERE room_id = NEW.room_id;
        END IF;
    ELSIF TG_OP = 'DELETE' THEN
        -- 删除学生时减少房间人数
        IF OLD.room_id IS NOT NULL THEN
            UPDATE rooms 
            SET current_num = current_num - 1
            WHERE room_id = OLD.room_id;
        END IF;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_room_occupancy
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW EXECUTE FUNCTION update_room_occupancy();

-- 检查房间容量的触发器
CREATE OR REPLACE FUNCTION check_room_capacity()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF NEW.room_id IS NOT NULL THEN
            IF (SELECT current_num FROM rooms WHERE room_id = NEW.room_id) >= 
               (SELECT capacity FROM rooms WHERE room_id = NEW.room_id) THEN
                RAISE EXCEPTION '房间已满，无法分配更多学生';
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_room_capacity
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION check_room_capacity(); 